Data is often stored in one format or location for short-term storage, and then stored in a different format or location for long-term storage. For example, in on-line transaction processing (OLTP), data may be stored for a short term in one or more regional databases, and then compressed and transferred to a centralized data warehouse for long term storage. To transfer data from an OLTP system to a data warehouse, data is organized as it is entered into the OLTP system and then discrete portions of the organized data are transferred to the data warehouse when resources permit.
A typical approach for organizing data is storing the data in tables that are organized into rows and columns. The tables are further divided into non-overlapping “sub-tables” called table partitions. Each table partition may be considered a bucket, and each given record is placed into a table partition based on the value of a data item in a particular column(s), the “partition key column(s)”. For example, sales records may be stored in a table that is partitioned by “annual quarter”. Sales records are entered into one of four different table partitions based on the value of the data item in the ‘sales date’ column. After a new quarter begins, the table partition corresponding to the previous quarter is scheduled for being compressed and transferred to a final destination in a data warehouse. Transferring in this case means to remove the data from the original sales table and only keep it in the final destination of the warehouse.
Compressing and transferring data from a source partition to a data warehouse may consume a large amount of time and computing resources depending on the number of records in the partition. Thus, rather than immediately transferring the data of a source partition, a metadata operation is used to separate (“remove”) the records of the source partition from the rest of the table and to perform the actual transfer at a later point in time in a time window of lesser activity. To perform the metadata operation, a new table is created that has the same schema (column names and data types) as the table of the source partition. The metadata for the new table has pointer data that references an empty table in persistent storage. The metadata for the source partition has pointer data that references a set of records in persistent storage. When the records of the source partition need to be separated (removed from the original table), the pointer data of the source partition is swapped with the pointer data of the new table without physically moving the records of the source partition. Thus, the partition's records are effectively transferred into the new table by doing a pure metadata operation.
SQL statements exist to create a table from another table's schema. For example, a create table as select (CTAS) statement with a filter predicate that is “false”, creates a new empty table based on the schema of a source partition:
CREATE TABLE new_table AS SELECT*FROM source_table WHERE 1=0;
Errors in the exchange process arise when the schema used to create the new table does not match how the source table stores and organizes the data in persistent storage. These errors occur when the properties of a table, such as column order, are exposed to users differently than how the data is organized in persistent storage. When the schema exposed by the CTAS statement is different than how the source table organizes data internally, the pointer data for the new table and the pointer data for the source partition end up referencing the wrong column positions after the exchange. When trying to access records in the new table or create records in the source table, the database management system accesses data items at locations and offsets that are incorrect, and the entire system breaks down.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.